﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using LotteryVote.Entity;
using System.Data.SqlClient;

namespace LotteryVote.Data
{
    public class BetOrderDataAccess : DataBase
    {
        public void Insert(BetOrder order)
        {
            string sql = string.Format(@"INSERT INTO {0} ({1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12}) VALUES 
(@{1},@{2},@{3},@{4},@{5},@{6},@{7},@{8},@{9},@{10},@{11},@{12}) SELECT IDENT_CURRENT('{0}')", BetOrder.TABLENAME, BetOrder.SHEETID,
            BetOrder.NUM, BetOrder.GAMEPLAYWAYID, BetOrder.COMPANYID, BetOrder.AMOUNT, BetOrder.TURNOVER, BetOrder.ODDS,
            BetOrder.COMMISSION, BetOrder.NET, BetOrder.NETAMOUNT, BetOrder.STATUS, BetOrder.DROPWATER);
            object id = base.ExecuteScalar(sql, new SqlParameter(BetOrder.SHEETID, order.SheetId),
                new SqlParameter(BetOrder.NUM, order.Num),
                new SqlParameter(BetOrder.GAMEPLAYWAYID, order.GamePlayWayId),
                new SqlParameter(BetOrder.COMPANYID, order.CompanyId),
                new SqlParameter(BetOrder.AMOUNT, order.Amount),
                new SqlParameter(BetOrder.TURNOVER, order.Turnover),
                new SqlParameter(BetOrder.ODDS, order.Odds),
                new SqlParameter(BetOrder.COMMISSION, order.Commission),
                new SqlParameter(BetOrder.NET, order.Net),
                new SqlParameter(BetOrder.NETAMOUNT, order.NetAmount),
                new SqlParameter(BetOrder.STATUS, order.Status),
                new SqlParameter(BetOrder.DROPWATER, order.DropWater));
            order.OrderId = Convert.ToInt32(id);
        }
        public void Update(BetOrder order)
        {
            string sql = string.Format(@"UPDATE {0} SET {1}=@{1},{2}=@{2},{3}=@{3} WHERE {4}=@{4}", BetOrder.TABLENAME, BetOrder.STATUS, BetOrder.DROPWATER, BetOrder.DRAWRESULT, BetOrder.ORDERID);
            base.ExecuteNonQuery(sql, new SqlParameter(BetOrder.STATUS, (int)order.Status),
                new SqlParameter(BetOrder.DROPWATER, order.DropWater),
                new SqlParameter(BetOrder.DRAWRESULT, order.DrawResult),
                new SqlParameter(BetOrder.ORDERID, order.OrderId));
        }
        public void UpdateState(int orderId, BetStatus status)
        {
            string updateCancelTime = (status == BetStatus.Invalid) ? ", " + BetOrder.CANCELTIME + "=getdate()" : string.Empty;
            string sql = string.Format(@"UPDATE {0} SET {1}=@{1} {2} WHERE {3}=@{3}", BetOrder.TABLENAME,
                BetOrder.STATUS, updateCancelTime, BetOrder.ORDERID);
            base.ExecuteNonQuery(sql, new SqlParameter(BetOrder.STATUS, (int)status),
                new SqlParameter(BetOrder.ORDERID, orderId));
        }
        public void SetDrawResult(BetOrder order)
        {
            string sql = string.Format(@"UPDATE {0} SET {1}=@{1} WHERE {2}=@{2}", BetOrder.TABLENAME, BetOrder.DRAWRESULT, BetOrder.ORDERID);
            base.ExecuteNonQuery(sql, new SqlParameter(BetOrder.DRAWRESULT, order.DrawResult),
                new SqlParameter(BetOrder.ORDERID, order.OrderId));
        }

        public BetOrder GetBetOrder(int orderId)
        {
            string sql = string.Format(@"SELECT bo.*,bs.{4} FROM {0} bo JOIN {2} bs on bs.{3}=bo.{3} WHERE bo.{1}=@{1}", BetOrder.TABLENAME, BetOrder.ORDERID,
                BetSheet.TABLENAME, BetSheet.SHEETID, BetSheet.USERID);
            return base.ExecuteEntity<BetOrder>(sql, new SqlParameter(BetOrder.ORDERID, orderId));
        }
        public IList<BetOrder> ListOrderBySheet(int sheetId)
        {
            string sql = string.Format(@"SELECT * FROM {0} WHERE {1}=@{1}", BetOrder.TABLENAME, BetOrder.SHEETID);
            return base.ExecuteList<BetOrder>(sql, new SqlParameter(BetOrder.SHEETID, sheetId));
        }
        public IList<BetOrder> ListOrderBySheet(int sheetId, int pageItemCount, int pageIndex)
        {
            string sql = string.Format(@"SELECT * FROM 
(
     SELECT ROW_NUMBER() OVER(ORDER BY {2}) AS RowNumber,* 
     FROM {0} where {1}=@{1}
) T
WHERE RowNumber BETWEEN {3} AND {4}", BetOrder.TABLENAME, BetOrder.SHEETID, BetOrder.ORDERID, (pageIndex - 1) * pageItemCount + 1, pageItemCount * pageIndex);
            return base.ExecuteList<BetOrder>(sql, new SqlParameter(BetOrder.SHEETID, sheetId));
        }
        public IList<BetOrder> ListOrder(BetStatus status, DateTime day)
        {
            string sql = string.Format(@"SELECT bo.*,bs.{6} FROM {0} as bo JOIN {3} bs on bs.{4}=bo.{5} WHERE bo.{1}=@{1} AND DAY(bo.{2})=DAY(@{2})",
                BetOrder.TABLENAME, BetOrder.STATUS, BetOrder.CREATETIME, BetSheet.TABLENAME, BetSheet.SHEETID, BetOrder.SHEETID, BetSheet.USERID);
            return base.ExecuteList<BetOrder>(sql, new SqlParameter(BetOrder.STATUS, (int)status),
                new SqlParameter(BetOrder.CREATETIME, day));
        }
        public IList<BetOrder> ListOrder(BetStatus status, int companyId, DateTime date)
        {
            string sql = string.Format(@"SELECT bo.*,bs.{6} FROM {0} as bo JOIN {3} bs on bs.{4}=bo.{5} WHERE bo.{1}=@{1} AND DAY(bo.{2})=DAY(@{2}) AND bo.{7}=@{7}",
                BetOrder.TABLENAME, BetOrder.STATUS, BetOrder.CREATETIME, BetSheet.TABLENAME, BetSheet.SHEETID, BetOrder.SHEETID, BetSheet.USERID, BetOrder.COMPANYID);
            return base.ExecuteList<BetOrder>(sql, new SqlParameter(BetOrder.STATUS, (int)status),
                new SqlParameter(BetOrder.CREATETIME, date),
                new SqlParameter(BetOrder.COMPANYID, companyId));
        }
        public IList<BetOrder> ListOrder(BetStatus status, DateTime day, int pageItemCount, int pageIndex)
        {
            int prePageItemCount = (pageIndex - 1) * pageItemCount + 1;
            string sql = string.Format(@";WITH CTE AS(select bo.*,bs.{9},bs.UserId,u.UserName from {0} bo
join {1} bs on bs.{2}=bo.{3}
join tb_User u on u.UserId=bs.UserId
where DAY(bo.{4})=DAY(@{4}) AND bo.{8}=@{8}) SELECT TOP {6} *
FROM CTE
WHERE ({5} >=
          (SELECT MAX({5})
         FROM (SELECT TOP {7} *
                 FROM CTE
                 ORDER BY {5}) AS T))
ORDER BY {5}", BetOrder.TABLENAME, BetSheet.TABLENAME, BetSheet.SHEETID, BetOrder.SHEETID,
             BetOrder.CREATETIME, BetOrder.ORDERID, pageItemCount, prePageItemCount, BetOrder.STATUS, BetSheet.IPADDRESS);
            return base.ExecuteList<BetOrder>(sql, new SqlParameter(BetOrder.CREATETIME, day),
                new SqlParameter(BetOrder.STATUS, (int)status));
        }
        public IList<BetOrder> ListOrderByCondition(BetStatus status, DateTime date, string num, int companyId,
            int gameplaywayId, string orderField, string sortType, int pageItemCount, int pageIndex)
        {
            string condition;
            var parameterList = BuildCondition(num, companyId, gameplaywayId, out condition, "bo.");
            condition = string.IsNullOrEmpty(condition) ? condition : " AND " + condition;
            int from = (pageIndex - 1) * pageItemCount;
            int to = pageIndex * pageItemCount;
            string sql = string.Format(@"WITH CTE AS(select bo.*,bs.{10},bs.{6},u.{11} from {0} bo
join {1} bs on bs.{2}=bo.{3}
join {4} u on u.{5}=bs.{6}
where DAY(bo.{7})=DAY(@{7}) and bo.{8}=@{8} {9} )
SELECT * FROM 
(
     SELECT ROW_NUMBER() OVER(ORDER BY {12} {13}) AS RowNumber,* 
     FROM CTE
) T
WHERE RowNumber BETWEEN {14} AND {15}", BetOrder.TABLENAME, BetSheet.TABLENAME, BetSheet.SHEETID, BetOrder.SHEETID,
            User.TABLENAME, User.USERID, BetSheet.USERID, BetOrder.CREATETIME, BetOrder.STATUS, condition, BetSheet.IPADDRESS, User.USERNAME,
            orderField, sortType, from, to);
            parameterList.Add(new SqlParameter(BetOrder.CREATETIME, date));
            parameterList.Add(new SqlParameter(BetOrder.STATUS, (int)status));
            return base.ExecuteList<BetOrder>(sql, parameterList.ToArray());
        }
        public IList<BetOrder> ListOrder(int userId, BetStatus status, DateTime day)
        {
            string sql = string.Format(@"SELECT BO.* FROM {0} BO
JOIN {1} BS ON BS.{2}=BO.{3}
WHERE BO.{4}=@{4} AND DAY(BO.{5})=DAY(@{5}) AND BS.{6}=@{6}",
                 BetOrder.TABLENAME, BetSheet.TABLENAME, BetSheet.SHEETID, BetOrder.SHEETID,
                 BetOrder.STATUS, BetOrder.CREATETIME, BetSheet.USERID);
            return base.ExecuteList<BetOrder>(sql, new SqlParameter(BetOrder.STATUS, (int)status),
                new SqlParameter(BetOrder.CREATETIME, day),
                new SqlParameter(BetSheet.USERID, userId));
        }
        public IList<BetOrder> ListOrder(int userId, BetStatus status, DateTime fromDate, DateTime toDate)
        {
            string sql = string.Format(@"SELECT BO.* FROM {0} BO
JOIN {1} BS ON BS.{2}=BO.{3}
WHERE BO.{4}=@{4} AND (DAY(BO.{5}) BETWEEN DAY(@{7}) AND DAY(@{8})) AND BS.{6}=@{6}",
                 BetOrder.TABLENAME, BetSheet.TABLENAME, BetSheet.SHEETID, BetOrder.SHEETID,
                 BetOrder.STATUS, BetOrder.CREATETIME, BetSheet.USERID, "FromDate", "ToDate");
            return base.ExecuteList<BetOrder>(sql, new SqlParameter(BetOrder.STATUS, (int)status),
                new SqlParameter(BetSheet.USERID, userId),
                new SqlParameter("FromDate", fromDate),
                new SqlParameter("ToDate", toDate));
        }
        public IList<BetOrder> ListOrder(int userId, int companyId, BetStatus status, DateTime date)
        {
            string sql = string.Format(@"SELECT BO.* FROM {0} BO
JOIN {1} BS ON BS.{2}=BO.{3}
WHERE BO.{4}=@{4} AND DAY(BO.{5})=DAY(@{5}) AND BS.{6}=@{6} AND BO.{7}=@{7}",
                BetOrder.TABLENAME, BetSheet.TABLENAME, BetSheet.SHEETID, BetOrder.SHEETID,
                BetOrder.STATUS, BetOrder.CREATETIME, BetSheet.USERID, BetOrder.COMPANYID);
            return base.ExecuteList<BetOrder>(sql, new SqlParameter(BetOrder.STATUS, (int)status),
                new SqlParameter(BetOrder.CREATETIME, date),
                new SqlParameter(BetSheet.USERID, userId),
                new SqlParameter(BetOrder.COMPANYID, companyId));
        }
        public IList<BetOrder> ListOrder(int userId, DateTime date, int pageItemCount, int pageIndex)
        {
            string sql = string.Format(@";WITH CTE AS(
select bo.* from {0} bo
join {1} bs on bs.{2}=bo.{3}
where DAY(bo.{4})=DAY(@{4}) AND bs.{5}=@{5})
SELECT * FROM 
(
     SELECT ROW_NUMBER() OVER(ORDER BY {6}) AS RowNumber,* 
     FROM CTE
) T
WHERE RowNumber BETWEEN {7} AND {8}", BetOrder.TABLENAME, BetSheet.TABLENAME, BetSheet.SHEETID, BetOrder.SHEETID, BetOrder.CREATETIME, BetSheet.USERID, BetOrder.ORDERID,
                                 (pageIndex - 1) * pageItemCount + 1, pageIndex * pageItemCount);
            return base.ExecuteList<BetOrder>(sql, new SqlParameter(BetSheet.USERID, userId),
                new SqlParameter(BetOrder.CREATETIME, date));
        }
        /// <summary>
        /// 返回指定用户的所有子孙的注单.
        /// </summary>
        /// <param name="user">The user.</param>
        /// <param name="status">The status.</param>
        /// <param name="date">The date.</param>
        /// <returns></returns>
        public IList<BetOrder> ListDescendantOrder(int userId, BetStatus status, DateTime date)
        {
            string sql = string.Format(@";WITH UserTree AS
(
SELECT * FROM {0}  WHERE {1}=@{1}
UNION ALL
SELECT B.* FROM {0} AS B,UserTree AS C WHERE B.{2}=C.{1} and B.{1}>C.{1}
)
select * from {3} bo
join {4} bs on bs.{5}=bo.{6}
join UserTree ut on ut.{1}=bs.{7}
where bo.{8}=@{8} and DAY(bs.{9})=DAY(@{9})", User.TABLENAME, User.USERID, User.PARENTID, BetOrder.TABLENAME, BetSheet.TABLENAME,
                    BetSheet.SHEETID, BetOrder.SHEETID, BetSheet.USERID, BetOrder.STATUS, BetSheet.CREATETIME);
            return base.ExecuteList<BetOrder>(sql, new SqlParameter(User.USERID, userId),
                new SqlParameter(BetOrder.STATUS, (int)status),
                new SqlParameter(BetSheet.CREATETIME, date));
        }
        public IList<BetOrder> ListDescendantOrder(int userId, BetStatus status, DateTime fromDate, DateTime toDate)
        {
            string sql = string.Format(@";WITH UserTree AS
(
SELECT * FROM {0}  WHERE {1}=@{1}
UNION ALL
SELECT B.* FROM {0} AS B,UserTree AS C WHERE B.{2}=C.{1} and B.{1}>C.{1}
)
select * from {3} bo
join {4} bs on bs.{5}=bo.{6}
join UserTree ut on ut.{1}=bs.{7}
where bo.{8}=@{8} and DAY(bs.{9}) BETWEEN DAY(@{10}) AND DAY(@{11})", User.TABLENAME, User.USERID, User.PARENTID, BetOrder.TABLENAME, BetSheet.TABLENAME,
                    BetSheet.SHEETID, BetOrder.SHEETID, BetSheet.USERID, BetOrder.STATUS, BetSheet.CREATETIME, "FromDate", "ToDate");
            return base.ExecuteList<BetOrder>(sql, new SqlParameter(User.USERID, userId),
                new SqlParameter(BetOrder.STATUS, (int)status),
                new SqlParameter("FromDate", fromDate),
                new SqlParameter("ToDate", toDate));
        }
        public int CountOrder(int sheetId)
        {
            string sql = string.Format(@"SELECT COUNT(0) FROM {0} WHERE {1}=@{1}", BetOrder.TABLENAME, BetOrder.SHEETID);
            object count = base.ExecuteScalar(sql, new SqlParameter(BetOrder.SHEETID, sheetId));
            return Convert.ToInt32(count);
        }
        public int CountOrder(int userId, DateTime date)
        {
            string sql = string.Format(@"SELECT COUNT(0) FROM {0} BO
JOIN {1} BS ON BS.{2} =BO.{3}
WHERE BS.{4}=@{4} AND DAY(BO.{5})=DAY(@{5})", BetOrder.TABLENAME, BetSheet.TABLENAME, BetSheet.SHEETID, BetOrder.SHEETID,
                                                        BetSheet.USERID, BetOrder.CREATETIME);
            object count = base.ExecuteScalar(sql, new SqlParameter(BetSheet.USERID, userId),
                new SqlParameter(BetOrder.CREATETIME, date));
            return Convert.ToInt32(count);
        }
        public int CountOrder(BetStatus status, DateTime date)
        {
            string sql = string.Format(@"SELECT COUNT(0) FROM {0} WHERE {1}=@{1} AND DAY({2})=DAY(@{2})", BetOrder.TABLENAME, BetOrder.STATUS, BetOrder.CREATETIME);
            object count = base.ExecuteScalar(sql, new SqlParameter(BetOrder.STATUS, (int)status),
                new SqlParameter(BetOrder.CREATETIME, date));
            return Convert.ToInt32(count);
        }
        public int CountOrder(BetStatus status, DateTime date, string num, int companyId, int gameplaywayId)
        {
            string condition;
            var parameterList = BuildCondition(num, companyId, gameplaywayId, out condition);
            condition = string.IsNullOrEmpty(condition) ? condition : " AND " + condition;
            string sql = string.Format(@"SELECT COUNT(0) FROM {0} WHERE {1}=@{1} AND DAY({2})=DAY(@{2}) {3}", BetOrder.TABLENAME, BetOrder.STATUS, BetOrder.CREATETIME, condition);
            parameterList.Add(new SqlParameter(BetOrder.STATUS, (int)status));
            parameterList.Add(new SqlParameter(BetOrder.CREATETIME, date));
            object count = base.ExecuteScalar(sql, parameterList.ToArray());
            return Convert.ToInt32(count);
        }

        private IList<SqlParameter> BuildCondition(string num, int companyId, int gameplaywayId, out string condit, string prefix = "")
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            StringBuilder condition = new StringBuilder();
            Action<string> appendCondition = (it) =>
            {
                if (condition.Length > 0)
                    condition.Append(" And ");
                condition.AppendFormat("{1}{0}=@{0}", it, prefix);
            };
            if (!string.IsNullOrEmpty(num))
            {
                appendCondition(BetOrder.NUM);
                parameterList.Add(new SqlParameter(BetOrder.NUM, num));
            }
            if (companyId > 0)
            {
                appendCondition(BetOrder.COMPANYID);
                parameterList.Add(new SqlParameter(BetOrder.COMPANYID, companyId));
            }
            if (gameplaywayId > 0)
            {
                appendCondition(BetOrder.GAMEPLAYWAYID);
                parameterList.Add(new SqlParameter(BetOrder.GAMEPLAYWAYID, gameplaywayId));
            }
            condit = condition.ToString();
            return parameterList;
        }
    }
}
